# coding:utf-8
"""
抖音数据存储操作
"""
import pymysql
import time
import yaml

from common_var import CommonVar

class MysqlDBHandler:
    """
    后台数据库操作
    """

    def __init__(self):
        config_file = open("./config.yaml", "r", encoding="UTF-8")
        db_config = yaml.load(config_file.read())
        config_file.close()
        self.dbobj = None
        self.hostname = db_config["DataBase"]["hostname"]
        self.port = db_config["DataBase"]["port"]
        self.user = db_config["DataBase"]["user"]
        self.passwd = db_config["DataBase"]["password"]
        self.dbname = db_config["DataBase"]["dbname"]
        self.charset = db_config["DataBase"]["charset"]
        self.dbcon()



    def dbcon(self):
        """
        :return: 获取数据库连接
        """
        current_date = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())

        if not self.dbobj:
            self.dbobj = pymysql.connect(host=self.hostname, port=self.port, user=self.user, password=self.passwd,
                                         db=self.dbname, charset=self.charset, read_timeout=3, write_timeout=3)
            self.dbobj.autocommit(True)
            print(
                "[INFO {}]数据库连接成功,连接信息【{}-{}】".format(current_date, self.hostname, self.dbname))

    #USER_PASSWORD表添加记录
    def user_password_insert(self,username,password,status):
        cur = self.dbobj.cursor()
        insert_sql = "insert into {} set {}='{}',{}='{}',{}={}".format(CommonVar.USER_PASSWORD, CommonVar.USER_PASSWORD_NAME,
                                                                               username, CommonVar.PASSWORD, password,CommonVar.USER_PASSWORD_STATUS,status
                                                                               )
        # print(insert_sql)
        cur.execute(insert_sql)
        self.dbobj.commit()

    # USER_PASSWORD表修改记录
    def user_password_update(self,username,password,status):
        cur = self.dbobj.cursor()
        update_sql = "update {} set {}='{}',{}={} where {}='{}'".format(CommonVar.USER_PASSWORD, CommonVar.PASSWORD, password,CommonVar.USER_PASSWORD_STATUS,status,
                                                                            CommonVar.USER_PASSWORD_NAME, username)
        # print(update_sql)
        cur.execute(update_sql)
        self.dbobj.commit()

    def user_password_select(self,username=None,user_list=None):
        if not user_list or len(user_list)==1:
            if user_list:
                cur = self.dbobj.cursor()
                sql = "select * from {} where {} = '{}'".format(CommonVar.USER_PASSWORD, CommonVar.USER_PASSWORD_NAME,
                                                                user_list[0])
                print(sql)
                cur.execute(sql)
                data = cur.fetchall()
                self.dbobj.commit()
                return data
            else:
                cur = self.dbobj.cursor()
                sql = "select * from {} where {} = '{}'".format(CommonVar.USER_PASSWORD, CommonVar.USER_PASSWORD_NAME, username)
                print(sql)
                cur.execute(sql)
                data = cur.fetchall()
                self.dbobj.commit()
                return data

        else:
            cur = self.dbobj.cursor()
            sql = "select * from {} where {} in {}".format(CommonVar.USER_PASSWORD, CommonVar.USER_PASSWORD_NAME,
                                                            user_list)
            # print(sql)
            cur.execute(sql)
            data = cur.fetchall()
            self.dbobj.commit()
            return data

    #userinfo表查询
    def user_site_select(self,username,site=None):
        if not site:
            cur = self.dbobj.cursor()
            sql = "select * from {} where {} = '{}'".format(CommonVar.USER_SITE,CommonVar.USER_SITE_NAME, username)
            # print(sql)
            cur.execute(sql)
            data = cur.fetchall()
            self.dbobj.commit()
            return data
        else:
            cur = self.dbobj.cursor()
            sql = "select * from {} where {}='{}' and {}='{}'".format(CommonVar.USER_SITE, CommonVar.USER_SITE_NAME, username,
                                                                           CommonVar.USER_SITE_SITE, site)
            # print(sql)
            cur.execute(sql)
            data = cur.fetchall()
            self.dbobj.commit()
            return data


    # userinfo表添加记录
    def user_site_insert(self,username,site):
        cur = self.dbobj.cursor()
        insert_sql = "insert into {} set {}='{}',{}='{}'".format(CommonVar.USER_SITE,CommonVar.USER_SITE_NAME,username,CommonVar.USER_SITE_SITE,site)
        # print(insert_sql)
        cur.execute(insert_sql)
        self.dbobj.commit()

    # # userinfo表改记录
    # def userinfo_update(self,username,password,status):
    #     cur = self.dbobj.cursor()
    #     update_sql = "update {} set {}='{}',{}='{}' where '{}'='{}'".format(CommonVar.USER_NIFO,CommonVar.SITE_USER,status,CommonVar.PASSWORD,password,CommonVar.USER_NAME,username)
    #     # print(update_sql)
    #     cur.execute(update_sql)
    #     self.dbobj.commit()

    # userinfo表删除
    # 记录
    # def userinfo_delete(self,username):
    #     cur = self.dbobj.cursor()
    #     delete_sql = "delete from {} where '{}'='{}'".format(CommonVar.USER_NIFO, CommonVar.USER_NAME, username)
    #     cur.execute(delete_sql)
    #     self.dbobj.commit()

    # #ticket_details表查询
    # def ticket_select(self,username,site=None,start_time=None,status=None,input_group=None,tracking_number=None,end_time=None,artificial_status=None):
    #     s="site, input_group, base_type, base_call_number, tracking_number, base_call_time, base_receiving_address, problem_info_type, problem_info_describe, callback_status1, callback_status2, callback_status3, sms_status, robot_status, artificial_status"
    #     d = {CommonVar.TICKET_USERNAMe: username, CommonVar.SITE: site,
    #          CommonVar.ADD_DATABASE_TIME: [start_time, end_time], CommonVar.ROBOT_STATUS: status,
    #          CommonVar.INPUT_GROUP: input_group, CommonVar.TRACKING_NUMBER: tracking_number,
    #          "artificial_status": artificial_status}
    #     conditions = ""
    #     for k in d.keys():
    #         if d[k] and k != "add_database_time":
    #             conditions += "{}='{}' and ".format(k, d[k])
    #         elif k == "add_database_time":
    #             if d[k][1]:
    #                 conditions += k + ">=" + "'{}'".format(d[k][0]) + " and " + k + "<=" + "'{}'".format(d[k][1])
    #             elif d[k][0]:
    #                 conditions += "to_days({}) = to_days('{}') and ".format(k, d[k][0])
    #
    #     cur = self.dbobj.cursor()
    #     sql = "select {} from {} where {}".format(s,CommonVar.TICKET_DETAILS,conditions.rstrip("and "))
    #     # print(sql)
    #     cur.execute(sql)
    #     data = cur.fetchall()
    #     self.dbobj.commit()
    #     return data

    # #更新ticket_details 跟进状态
    # def ticket_update(self,artificial_status,tracking_number):
    #     cur = self.dbobj.cursor()
    #     update_sql = "update {} set artificial_status='{}' where {}='{}'".format(CommonVar.TICKET_DETAILS,artificial_status,CommonVar.TRACKING_NUMBER, tracking_number)
    #     cur.execute(update_sql)
    #     self.dbobj.commit()

    # v2.0 ticket_details表查询 #todo
    def ticket_td(self,info_datas):
        aa=()
        datad =()
        for i in info_datas:
            site = i["site"]
            inputGroup = i["inputGroup"]
            baseType = i["baseType"]
            baseCallNumber = i["baseCallNumber"]
            trackingNumber = i["trackingNumber"]
            baseCallTime = i["baseCallTime"]
            baseReceivingAddress = i["baseReceivingAddress"]
            problemType = i["problemType"]
            problemInfoDescribe = i["problemInfoDescribe"]
            callbackStatus1 = i["callbackStatus1"]
            callOneTime = i["callOneTime"]
            oneIvrTime = i["oneIvrTime"]
            callbackStatus2 = i["callbackStatus2"]
            callTwoTime = i["callTwoTime"]
            twoIvrTime = i["twoIvrTime"]
            callbackStatus3 = i["callbackStatus3"]
            callThreeTime = i["callThreeTime"]
            threeIvrTime = i["threeIvrTime"]
            remark =i["remark"]
            smsStatus = i["smsStatus"]
            robotStatus = i["robotStatus"]
            artificialStatus = i["artificialStatus"]
            id = i["ticketId"]
            aa = aa + (site, inputGroup, baseType, baseCallNumber, trackingNumber, baseCallTime, baseReceivingAddress, problemType,
            problemInfoDescribe, callbackStatus1,callOneTime,oneIvrTime,callbackStatus2,callTwoTime,twoIvrTime,callbackStatus3,callThreeTime,threeIvrTime,remark,smsStatus, robotStatus,artificialStatus,id)
            # print(aa)
            datad = datad + (aa,)
            aa = tuple()

        return datad
